package com.matrix.framework.sysconfig.iplist.repository;

import com.matrix.framework.sysconfig.iplist.data.IpAccessRecordPo;
import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

/**
 * IP访问记录Repository接口
 *
 * Copyright © 海平面工作室 版权所有
 *
 * @Author: Leo
 * @Create: 2024/12/1 15:30
 * @Since 1.2
 */
public interface IpAccessRecordRepository extends ReactiveCrudRepository<IpAccessRecordPo, Long> {

    /**
     * 根据IP地址和时间范围查找访问记录
     */
    @Query("SELECT * FROM security_ip_access_record WHERE ip_address = :ipAddress AND access_time >= :startTime ORDER BY access_time DESC")
    Flux<IpAccessRecordPo> findByIpAddressAndTimeRange(String ipAddress, long startTime);

    /**
     * 根据用户名查找访问记录
     */
    @Query("SELECT * FROM security_ip_access_record WHERE username = :username ORDER BY access_time DESC LIMIT :limit")
    Flux<IpAccessRecordPo> findByUsername(String username, int limit);

    /**
     * 根据API路径查找访问记录
     */
    @Query("SELECT * FROM security_ip_access_record WHERE api_path = :apiPath ORDER BY access_time DESC LIMIT :limit")
    Flux<IpAccessRecordPo> findByApiPath(String apiPath, int limit);

    /**
     * 根据结果类型查找访问记录
     */
    @Query("SELECT * FROM security_ip_access_record WHERE result = :result ORDER BY access_time DESC LIMIT :limit")
    Flux<IpAccessRecordPo> findByResult(String result, int limit);

    /**
     * 删除指定时间之前的记录
     */
    @Query("DELETE FROM security_ip_access_record WHERE create_time < :cutoffTime")
    Mono<Long> deleteByCreateTimeBefore(long cutoffTime);

    /**
     * 统计指定IP的访问次数
     */
    @Query("SELECT COUNT(*) FROM security_ip_access_record WHERE ip_address = :ipAddress AND access_time >= :startTime")
    Mono<Long> countByIpAddressAndTimeRange(String ipAddress, long startTime);

    /**
     * 统计指定IP的成功访问次数
     */
    @Query("SELECT COUNT(*) FROM security_ip_access_record WHERE ip_address = :ipAddress AND result = 'SUCCESS' AND access_time >= :startTime")
    Mono<Long> countSuccessByIpAddressAndTimeRange(String ipAddress, long startTime);

    /**
     * 统计指定IP的失败访问次数
     */
    @Query("SELECT COUNT(*) FROM security_ip_access_record WHERE ip_address = :ipAddress AND result = 'FAILED' AND access_time >= :startTime")
    Mono<Long> countFailedByIpAddressAndTimeRange(String ipAddress, long startTime);

    /**
     * 统计指定IP的阻止访问次数
     */
    @Query("SELECT COUNT(*) FROM security_ip_access_record WHERE ip_address = :ipAddress AND result = 'BLOCKED' AND access_time >= :startTime")
    Mono<Long> countBlockedByIpAddressAndTimeRange(String ipAddress, long startTime);
} 